﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace PTTK19.DB
{
    class DBMS
    {
        private string _connectionstring = "";
        private SqlConnection _conn = null;
        private SqlCommandBuilder sb = new SqlCommandBuilder();
        private SqlTransaction tran;
        public string ConnectionString
        {
            get
            {
                return _connectionstring;
            }
            set
            {
                _connectionstring = value;
            }
        }
        public DBMS()
        {
            _connectionstring = @"Server=.;Database=HuongRung_Team_19;Integrated Security=true";
        }
        public void beginTrans()
        {
            tran = _conn.BeginTransaction();
        }
        public void Connect()
        {
            try
            {
                _conn = new SqlConnection(_connectionstring);
                _conn.Open();
            }
            catch (Exception e)
            {
                throw new Exception("Không kết nối được cơ sở dữ liệu !!! " + e.Message);
            }
        }

        public void Disconnect()
        {
            _conn.Close();
            _conn = null;
        }
        public bool IsConnect()
        {
            return (_conn != null);
        }
        public void UpdateDatatTable(DataTable dtSP,string strSQL)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
            adapter.Update(dtSP);


        }
        public SqlDataReader ExecuteQuery(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteReader();

        }
        public object ExecuteScalar(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteScalar();
        }

        public int ExecuteNoneQuery(string strSQL)
        {

            SqlCommand _command = new SqlCommand(strSQL, _conn);
            int i = _command.ExecuteNonQuery();
            return i;
        }
       
        public DataTable ExcuteQueryTable(string strSQL)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
            DataTable ds = new DataTable("DATA");
            adapter.Fill(ds);
            return ds;
        }
        public DataSet ExcuteQueryDataSet(string strSQL)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds;
        }
        public DataTable ExcuteQueryDataTable(string strSQL)
        {
            DataSet ds = ExcuteQueryDataSet(strSQL);
            return ds.Tables[0];
        }
        public int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlParameter returnVal = new SqlParameter("returnVal", SqlDbType.Int);
                returnVal.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnVal);
                command.ExecuteNonQuery();
                return Convert.ToInt32(returnVal.Value);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() \n" + ex.Message);
            }
        }
        public DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        public void ExcuteProcedure(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi function DataService.ExcuteProcedure()!!! \n" + ex.Message);
            }
        }
    }
}